package com.terren.filter.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.terren.filter.config.Config;
import com.terren.filter.entity.OpinionAnalysis;

public class CRUD {
	
	
	public int update(String urlId) {
		int count = 0;
		Connection conn = JDBCHelper.getConn();
		PreparedStatement prepStmt = null;
		String sql = "UPDATE t_article_opinion_analysis SET `status` = 0 WHERE urlId= ?";
		try {
			prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, urlId);
			count = prepStmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JDBCHelper.closePrepStmt(prepStmt);
			JDBCHelper.closeConn(conn);
		}
		return count;
	}
	public List<OpinionAnalysis> selectOpinions() {
		List<OpinionAnalysis> list = new ArrayList<OpinionAnalysis>();
		Connection conn = JDBCHelper.getConn();
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		if(Config.LIMIT_TIME == null) {
			Date date = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
			Config.LIMIT_TIME = sdf.format(date);
		}
		String sql = "SELECT `urlid`, `url`, `title`, `summary`, `content`, `author`, `pubtime`, `newssource`, `opinion`, `updateTime`, `status` "
				+ "FROM `t_article_opinion_analysis`"
				+ "WHERE updateTime > '"+Config.LIMIT_TIME+"'";
		try {

			prepStmt = conn.prepareStatement(sql);
			rs = prepStmt.executeQuery();
			while(rs.next()){
				OpinionAnalysis opinion = new OpinionAnalysis();
				opinion.setUrlid(rs.getString("urlid"));
				opinion.setUrl(rs.getString("url"));
				opinion.setTitle(rs.getString("title"));
				opinion.setContent(rs.getString("content"));
				list.add(opinion);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCHelper.closeAll(rs, prepStmt, conn);
		}
		return list;
	}
	
	public List<String> selectKeyword() {
		List<String> list = new ArrayList<String>();
		Connection conn = JDBCHelper.getConn();
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		String sql = "SELECT keyword FROM t_spider_keyword WHERE sourceId = 1001 AND hashId = 0";
		try {
			prepStmt = conn.prepareStatement(sql);
			rs = prepStmt.executeQuery();
			while(rs.next()){
				list.add(rs.getString("keyword"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCHelper.closeAll(rs, prepStmt, conn);
		}
		return list;
	}
	
	/*
	 * public int save(OpinionAnalysis opinion) { int count = 0; Connection conn =
	 * JDBCHelper.getConn(); PreparedStatement prepStmt = null; String sql = ""; try
	 * { prepStmt = conn.prepareStatement(sql); prepStmt.setString(1,
	 * opinion.getAuthor()); prepStmt.setString(2, opinion.getContent()); count =
	 * prepStmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }
	 * finally{ JDBCHelper.closePrepStmt(prepStmt); JDBCHelper.closeConn(conn); }
	 * return count;
	 * 
	 * }
	 */
	
	/*
	 * public int delete(OpinionAnalysis opinion) { int count = 0; Connection conn =
	 * JDBCHelper.getConn(); PreparedStatement prepStmt = null; String sql = " ";
	 * try { prepStmt = conn.prepareStatement(sql); prepStmt.setInt(1,
	 * opinion.getOpinion()); count = prepStmt.executeUpdate(); } catch
	 * (SQLException e) { e.printStackTrace(); } finally{
	 * JDBCHelper.closePrepStmt(prepStmt); JDBCHelper.closeConn(conn); } return
	 * count; }
	 */
	
	/*
	 * public int update(OpinionAnalysis OpinionAnalysis) { int count = 0;
	 * Connection conn = JDBCHelper.getConn(); PreparedStatement prepStmt = null;
	 * String sql =
	 * " update OpinionAnalysis set OpinionAnalysisname = ?,password = ? where id = ?"
	 * ; try { prepStmt = conn.prepareStatement(sql); prepStmt.setString(1,
	 * OpinionAnalysis.getOpinionAnalysisname()); prepStmt.setString(2,
	 * OpinionAnalysis.getPassword()); prepStmt.setInt(3, OpinionAnalysis.getId());
	 * count = prepStmt.executeUpdate(); } catch (SQLException e) {
	 * e.printStackTrace(); } finally{ JDBCHelper.closePrepStmt(prepStmt);
	 * JDBCHelper.closeConn(conn); } return count; }
	 */
}
